package com.xx.questionnaire.util.dao;

import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

import javax.sql.DataSource;

import org.apache.commons.dbcp2.BasicDataSourceFactory;

 
/**
 * JDBC工具类<br>
 * @author Administrator
 *
 */
public final class JDBCUtil {
	static final String driver="com.mysql.jdbc.Driver";
	static final String url = "jdbc:mysql://localhost:3306/student";
	static final String dataUser = "root";
	static final String dataPassword = "123456";
	static  Connection connection;
  	private PreparedStatement ps ;
  	private Connection con =null;
	static{
		 
		
		 //jdbcConnection();
		 dbcpConnection();
		    
	}
	private static void dbcpConnection(){
		String dbcpUrl = "dbcp-mysql.properties";
		
		DataSource ds;
		Properties p  = new Properties();
		try {
			//通过类的类加载器将属性文件加载至输入流 
			p.load(JDBCUtil.class.getClassLoader().getResourceAsStream(dbcpUrl));
		 
			
				ds = BasicDataSourceFactory.createDataSource(p);
		connection = ds.getConnection();
			  
		} catch (IOException e) {
			e.printStackTrace();
		}catch (Exception e) {
			e.printStackTrace();
		}
		
		
		
	}
	
	 
	
	/**
	 * 以原生JDBC的方式连接数据库<br>
	 * @deprecated
	 */
	@SuppressWarnings("unused")
	private static void  jdbcConnection(){
		try {
			Class.forName(driver);
		} catch (ClassNotFoundException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
			System.out.println("驱动加载失败！");
		}
		 try {
			 System.out.println("自动匹配的Driver:"+DriverManager.getDriver(url));
		connection = DriverManager.getConnection(url, dataUser, dataPassword);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			System.out.println("数据库连接失败！");
			e.printStackTrace();
			
		}
	 System.out.println("总连接已建立！");
	}
	
	 /**
	  * 此方法主要是为了与jdbcConnection()相对应<br>
	  * 
	  * @param SQL :sql语句
	  * @return PreparedStatement对象
	  *  
	  */
	public PreparedStatement getPS(String SQL){
	       con = connection;
	       System.out.println("将辅连接引用值总连接！");
	       try {
	    	  
	    		   System.out.println("connection已经将连接赋予con了");
	    		   System.out.println("辅助连接类的哈希值   ："+con.hashCode());
	    		   System.out.println("主连接类的哈希值   ："+connection.hashCode());
	    	  
			ps = con.prepareStatement(SQL);  //预编译sql语句
			System.out.println("SQL语句；（"+SQL+"）已预编译并准备执行。");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	       return ps;
	       
	}
	 
 
	public void close(ResultSet rs){
		try {
			 
			rs.close();
			this.ps.close();
		    this.con = null; 
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		 
	}
	//Default Method: 关闭结果集对象  
    public  void closeDefault(ResultSet rs) {  
        Connection conn = null;  
        Statement stmt = null;  
        try {  
            if (rs == null) {  
                System.out.println("结果集为空，无法关闭！！");  
            } else {  
                stmt = rs.getStatement();  
                conn = stmt.getConnection();  
                rs.close();  
                if (stmt != null)  
                    stmt.close();  
                if (conn != null) { 
                    conn.close();
                    
                }
            }  
        } catch (Exception ex) {  
            ex.printStackTrace();  
        }  
    } 
    
    
	public static void main(String[] args) {
		JDBCUtil jdbc  = new JDBCUtil();
		String sql = "SELECT * from user where user_name=?";
		PreparedStatement ps = jdbc.getPS(sql);
		try {
			ps.setString(1, "wangzhihua");
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		System.out.println("运行成功");
		try {
			ResultSet rs = ps.executeQuery();
			while(rs.next()){
				System.out.println(rs.getString(1)+rs.getString(2));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
}
